package com.navi.loaddata.loadService.impl.Dcdata.cell1;

import com.navi.loaddata.core.errorLog.ErrorLog;
import com.navi.loaddata.core.etlException.ETLException;
import com.navi.loaddata.core.gpEntity.MArrayParamD;
import com.navi.loaddata.core.mesEntity.SpcDcResultData;
import com.navi.loaddata.core.mesEntity.SpcDcdata;
import com.navi.loaddata.core.mysqlEntity.EtlConfD;
import com.navi.loaddata.core.oracleEntity.cell1.EtlCellPdsComponentV;
import com.navi.loaddata.core.oracleEntity.cell1.EtlCellProductV;
import com.navi.loaddata.core.oracleEntity.cell1.EtlCellStepV;
import com.navi.loaddata.core.oracleEntity.cell1.EtlCellPdsComptSummaryV;
import com.navi.loaddata.core.oracleEntity.cell1.EtlCellPdsResultV;
import com.navi.loaddata.core.oracleEntity.cell1.EtlCellParamV;
import com.navi.loaddata.core.repository.impl.OracleRepositoryImpl;
import com.navi.loaddata.core.util.LoggerUtil;
import com.navi.loaddata.core.util.ProcessUtil;
import com.navi.loaddata.loadService.impl.baseData.cell1.LoadMesBaseDataServiceCELL1;
import com.navi.loaddata.loadService.impl.baseData.cell1.ProcessBaseDataServiceCELL1;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import static com.navi.loaddata.core.constant.GenericDef.*;
import static java.util.stream.Collectors.groupingBy;

/**
 * @author jay
 */
@Service
@Transactional(transactionManager = "thirdaryPlatformTransactionManager",
        rollbackFor = {RuntimeException.class, Exception.class})
public class LoadDcDataServiceCELL1 {
    private static final Logger logger = LoggerFactory.getLogger(LoadDcDataServiceCELL1.class);
    private LoggerUtil loggerUtil = new LoggerUtil(this.getClass());

    @Autowired
    private OracleRepositoryImpl dao3;

    @Autowired
    private LoadMesBaseDataServiceCELL1 loadMesBaseDataService;

    @Autowired
    private ProcessBaseDataServiceCELL1 processBaseDataService;
    @Autowired
    private ProcessDcDataServiceCELL1 processDcDataService;

    @Autowired
    private LoadDcInfoServiceCELL1 loadDcInfoService;

    public int loadDcDataMain(EtlConfD etlConfD) throws ETLException {
        int countSize = 0;
        Session session = dao3.getEntityManagerSession();
        try {
            countSize = loadDcData(session, etlConfD);
            logger.info("loadDcDataMain finish");
        } catch (ETLException e) {
            throw new ETLException(e.getMessage());
        }
        return countSize;
    }

    /**
     * Load Dc Data
     *
     * @param session
     * @param etlConfD
     */
    private int loadDcData(Session session, EtlConfD etlConfD) throws ETLException {
        int countSize = 0;


        // DcData
        List<SpcDcdata> spcDcdataEntityList = loadDcInfoService.loadDcDataList(etlConfD, "'"+DCTYPE_I+"','"+DCTYPE_P+"'");
        logger.info("Spc Dcdata count {}", spcDcdataEntityList.size());
        if (!spcDcdataEntityList.isEmpty()) {
            /**
             * etl_array_pds_result_v  & etl_array_pds_glass_summary_v
             *  1. PARAM_COLLECTION先固定，后续会从其PARAM表读取
             *  2. etl_array_pds_result_v 存row data （SITENAME可以转为数字的叫raw data, 如AVG/ASC就不包含在内）
             *  3. etl_array_pds_glass_summary_v 存汇总数据 （SITENAME 是字母的部分）
             * 		3.1 SITENAME = ASC => result 赋值给 PARAM_VALUE2, DATA_QTY 赋值 0
             * 		3.2 SITENAME = X   => result 赋值给 PARAM_VALUE, DATA_QTY 赋值 1
             * 			3.2.1 此时PARAM_VALUE,AVG,MAX, MIN 都填相同值（result）, RANGE  UI U2先空
             *      3.2 SITENAME  IN('AVG','MAX','MIN','SDV','RNG','U1','U2') => 赋值给对应的列
             * 	    3.3 DATA_QTY 记录 raw data的数量汇总
             * 		3.4 raw data >X > ASC
             * 	    3.5 PARAM_VALUE Default 资料顺位AVG-> X
             *  4. 根据主表数据的 Inspection 不同，分别查询 SPC_DCMEASUREDATARESULT & SPC_DCPROCESSDATARESULT
             *      4.1 Inspection => SPC_DCMEASUREDATARESULT
             *      4.2 Production => SPC_DCPROCESSDATARESULT
             * update
             * 	先固定处理 Inspection
             *
             */
// base
            List<EtlCellStepV> etlCfStepVList=new ArrayList<>();
            List<EtlCellProductV> etlCfProductVList=new ArrayList<>();
            try {
                etlCfStepVList = loadMesBaseDataService.loadAllFinalStep(SHOP_OC, ErrorLog.ERROR_PROCESS_ARRAY_STEP_V);
                etlCfProductVList = loadMesBaseDataService.loadAllFinalProduct(SHOP_OC,ErrorLog.ERROR_PROCESS_ARRAY_PRODUCT_V);
            }catch (Exception e){
                logger.error("Cannot excute loadAll()_BASE from [{}] to [{}]。", etlConfD.getRunStartTimestamp(), etlConfD.getRunEndTimestamp());
                throw new ETLException(e.getMessage());
            }
            // save base
            try {
                saveAll(etlCfStepVList,ErrorLog.ERROR_TABLE_ETL_ARRAY_STEP_V, "NULL");
                saveAll(etlCfProductVList,ErrorLog.ERROR_TABLE_ETL_ARRAY_PRODUCT_V, "NULL");
            } catch (Exception e) {
                logger.error("Cannot excute dao.saveAll()_BASE from [{}] to [{}]。", etlConfD.getRunStartTimestamp(), etlConfD.getRunEndTimestamp());
                throw new ETLException(e.getMessage());
            }

            // call base proc
            try {
                SQLQuery queryLoadStep = session.createSQLQuery("{call CELL1_BASE_LOADER.LOAD_STEP()}");
                queryLoadStep.executeUpdate();
            }catch (Exception e) {
                logger.error(e.getMessage());
                logger.error(LoggerUtil.stackTraceToString(e));
                logger.error("Cannot call procedure from [{}] to [{}]。", etlConfD.getRunStartTimestamp(), etlConfD.getRunEndTimestamp());
                throw new ETLException("ALL"+ErrorLog.ERROR_LOAD_ARRAY_STEP_V+e.getMessage());
            }
            try {
                SQLQuery queryLoadProduct = session.createSQLQuery("{call CELL1_BASE_LOADER.LOAD_PRODUCT()}");
                queryLoadProduct.executeUpdate();
            }catch (Exception e) {
                logger.error(e.getMessage());
                logger.error(LoggerUtil.stackTraceToString(e));
                logger.error("Cannot call procedure from [{}] to [{}]。", etlConfD.getRunStartTimestamp(), etlConfD.getRunEndTimestamp());
                throw new ETLException("ALL"+ErrorLog.ERROR_LOAD_ARRAY_PRODUCT_V+e.getMessage());
            }

            //param
            List<MArrayParamD> arrayParamList= loadMesBaseDataService.loadParamInfo(SHOP_OC);
            List<EtlCellParamV> etlarrayParamList = new ArrayList<>();
            try {
                etlarrayParamList = processBaseDataService.processArrayParamInfo(arrayParamList, ErrorLog.ERROR_PROCESS_ARRAY_PARAM_D);
            } catch (Exception e) {
                logger.error("Cannot excute process_param() from [{}] to [{}]。", etlConfD.getRunStartTimestamp(), etlConfD.getRunEndTimestamp());
                throw new ETLException("ALL:" + e.getMessage());
            }
            // save
            try {
                saveAll(etlarrayParamList, ErrorLog.ERROR_TABLE_ARRAY_PARAM_D, "ALL");
            } catch (Exception e) {
                logger.info("Cannot excute dao.saveAll() from [{}] to [{}]。", etlConfD.getRunStartTimestamp(), etlConfD.getRunEndTimestamp());
                throw new ETLException(e.getMessage());
            }
            // call proc
            try {
                SQLQuery queryDefect = session.createSQLQuery("{Call CELL1_BASE_LOADER.LOAD_PARAM()}");
                queryDefect.executeUpdate();
            } catch (Exception e) {
//                        System.out.println("======================================================================");
//                        System.out.println(curDCList.get(i).getMaterialname());
//                        System.out.println(curDCList.get(i).getProcessoperationname());
                logger.error(ErrorLog.ERROR_LOAD_ARRAY_PARAM_D + ":" + e.getMessage());
                logger.error(LoggerUtil.stackTraceToString(e));
                logger.error("Cannot call procedure from [{}] to [{}]。", etlConfD.getRunStartTimestamp(), etlConfD.getRunEndTimestamp());
                throw new ETLException("ALL:" + ErrorLog.ERROR_LOAD_ARRAY_PARAM_D + ":" + e.getMessage());
            }



            List<SpcDcdata> curDCList=spcDcdataEntityList;
//            //cmn glass
//            Map<String, List<SpcDcdata>> DCDataTree =
//                    spcDcdataEntityList.stream().collect(groupingBy(SpcDcdata::getMaterialname));
//            for (Map.Entry<String, List<SpcDcdata>> entry2 : DCDataTree.entrySet()) {
//                List<SpcDcdata> curDCList = entry2.getValue();
//                //common
//                List<MesProducthistory> commonGlassVList =
//                        loadMesBaseDataService.loadCommonGlassInfo(etlConfD, curDCList.get(0).getMaterialname());
//                if (commonGlassVList.size() == 0) {
//                    continue;
//                }
//                if (commonGlassVList.size() == 1) {
//                    System.out.println(curDCList.get(0).getMaterialname());
//                }
//                if (commonGlassVList.size() > 1) {
//                    System.out.println("==================");
//                }
//                List<EtlCellCmnComponentV> etlArrayCommonGlassVList = new ArrayList<>();
//                try {
//                    etlArrayCommonGlassVList = processBaseDataService.processCommonGlass(commonGlassVList, ErrorLog.ERROR_PROCESS_ARRAY_CMN_GLASS_V, logger);
//                } catch (Exception e) {
//                    logger.error("Cannot excute processCMN() from [{}] to [{}]。", etlConfD.getRunStartTimestamp(), etlConfD.getRunEndTimestamp());
//                    throw new ETLException(curDCList.get(0).getMaterialname() + ":" + e.getMessage());
//                }
////                logger.info("Array common glass count {}", commonGlassVList.size());
//                // save
//                try {
//                    saveAll(etlArrayCommonGlassVList, ErrorLog.ERROR_TABLE_ETL_ARRAY_CMN_GLASS_V, curDCList.get(0).getMaterialname());
//                } catch (Exception e) {
//                    logger.error(curDCList.get(0).getMaterialname()+":"+e.getMessage());
//                    logger.error("Cannot excute dao.saveCMN() from [{}] to [{}]。", etlConfD.getRunStartTimestamp(), etlConfD.getRunEndTimestamp());
//                    throw new ETLException(e.getMessage());
//                }
//                // call procedure
//                try {
//                    SQLQuery queryLoadCmnGlass = session.createSQLQuery("{call CF_MES_LOADER.LOAD_CMN_GLASS()}");
//                    queryLoadCmnGlass.executeUpdate();
//                } catch (Exception e) {
//                    logger.error(LoggerUtil.stackTraceToString(e));
//                    logger.error("Cannot call procedure from [{}] to [{}]。", etlConfD.getRunStartTimestamp(), etlConfD.getRunEndTimestamp());
//                    throw new ETLException(curDCList.get(0).getMaterialname() + ":" + ErrorLog.ERROR_LOAD_ARRAY_CMN_GLASS_V + ":" + e.getMessage());
//                }


                // DcData
                for (int i = 0; i < curDCList.size(); i++) {
                    //站点限制
                    if (true == (ProcessUtil.opeLimit(curDCList.get(i).getProcessoperationname()))) {
                        continue;
                    }

                    SpcDcdata spcDcdataEntity = curDCList.get(i);

                    List<SpcDcResultData> spcDcResultDataList =new ArrayList<>();
//                    List<SpcDcResultData> spcDcResultDataList2 = new ArrayList<>();
                    if (DCTYPE_I.equals(curDCList.get(i).getDctype())) {
                        spcDcResultDataList = loadDcInfoService.loadMDataList(etlConfD, spcDcdataEntity.getDcdataid());
                    }
                    if (DCTYPE_P.equals(curDCList.get(i).getDctype())) {
                        spcDcResultDataList = loadDcInfoService.loadPDataList(etlConfD, spcDcdataEntity.getDcdataid());
//                        spcDcResultDataList2.forEach(e ->{
//                            spcDcResultDataList1.add(e);
//                        });
                    }

                    //process
                    EtlCellPdsComponentV etlCfPdsGlassV = new EtlCellPdsComponentV();
                    try {
                        etlCfPdsGlassV = processDcDataService.processDcData(spcDcdataEntity, ErrorLog.ERROR_PROCESS_ARRAY_PDS_GLASS_V, logger);
                    } catch (Exception e) {
                        logger.error("Cannot excute process() from [{}] to [{}]。", etlConfD.getRunStartTimestamp(), etlConfD.getRunEndTimestamp());
                        throw new ETLException("process错误：" + curDCList.get(i).getMaterialname() + ":" + e.getMessage());
                    }
                    List<EtlCellPdsResultV> etlCfPdsResultVList = new ArrayList<>();
                    List<EtlCellPdsComptSummaryV> etlCellPdsComptSummaryVList = new ArrayList<>();

                    if (spcDcResultDataList.isEmpty()) {
                        //todo logger error
                        continue;
                    }

                    String paramCollection = spcDcdataEntity.getRecipename();
                    if (StringUtils.isEmpty(paramCollection)) {
//                        paramCollection = spcDcdataEntity.getProductspecname();
                        // 如果recipe为空，次笔数据不处理
                        logger.warn("当前EDC数据recipe为空，DCDATAID: {}", spcDcdataEntity.getDcdataid());
                        continue;
                    }


                    Map<String, List<SpcDcResultData>> mDataTree =
                            spcDcResultDataList.stream().collect(groupingBy(SpcDcResultData::getItemname));
                    for (Map.Entry<String, List<SpcDcResultData>> entry : mDataTree.entrySet()) {
                        EtlCellPdsComptSummaryV etlCellPdsComptSummaryV = new EtlCellPdsComptSummaryV();
                        List<SpcDcResultData> curDcDataList = entry.getValue();

                        boolean isRawData = false;
                        boolean isXData = false;
                        boolean isAscData = false;
                        int resultListCnt = curDcDataList.size();

                        // 数据检查
                        SpcDcResultData firstResult = curDcDataList.get(0);
                        String firstSiteName = firstResult.getSitename();
                        if (ProcessUtil.isNumeric(firstSiteName)) {
                            isRawData = true;
                        } else if (SITE_X.equals(firstSiteName)) {
                            isXData = true;
                            if (resultListCnt > 1) {
                                logger.error("Site name为X，但是result数量大于1条，DCDATAID: {}", spcDcdataEntity.getDcdataid());
//                            return SITE_DATA_X_INVALID_RAW_DATA_CONT;// todo  throw exception & return count is 0
                                continue;
                            }
                        } else if (SITE_ASC.equals(firstSiteName)) {
                            isAscData = true;
                            if (resultListCnt > 1) {
                                logger.error("Site name为ASC，但是result数量大于1条，DCDATAID: {}", spcDcdataEntity.getDcdataid());
//                            return SITE_DATA_ASC_INVALID_RAW_DATA_CONT;
                                continue;
                            }
                        } else {
//                            logger.error("无效Site name {}, DCDATAID: {}", firstSiteName, spcDcdataEntity.getDcdataid());
//                        return SITE_DATA_INVALID_SITENAME;
                            continue;
                        }

                        // 赋值
                        etlCellPdsComptSummaryV.setStepId(etlCfPdsGlassV.getStepId());
                        etlCellPdsComptSummaryV.setSubEquipId(etlCfPdsGlassV.getSubEquipId());
                        etlCellPdsComptSummaryV.setComponentId(etlCfPdsGlassV.getComponentId());
                        etlCellPdsComptSummaryV.setComponentEndTime(etlCfPdsGlassV.getComponentEndTime());
                        etlCellPdsComptSummaryV.setParamCollection(paramCollection);//TODO
                        etlCellPdsComptSummaryV.setParamName(firstResult.getItemname());
                        for (int j = 0; j < resultListCnt; j++) {
                            SpcDcResultData curResult = curDcDataList.get(j);
                            String curSiteName = curResult.getSitename();
                            Double resVal = ProcessUtil.getDouble(curResult.getResult());
                            if (isRawData) {
                                if (ProcessUtil.isNumeric(curSiteName)) {
                                    // 数字类型 raw data
                                    EtlCellPdsResultV result = new EtlCellPdsResultV();
                                    result.setStepId(spcDcdataEntity.getProcessoperationname());
                                    result.setSubEquipId(spcDcdataEntity.getUnitname());
                                    result.setComponentId(spcDcdataEntity.getMaterialname());
                                    result.setComponentEndTime(spcDcdataEntity.getEventtime());
                                    result.setParamCollection(paramCollection);
                                    result.setParamName(curResult.getItemname());
                                    result.setSiteName(curResult.getSitename());
                                    result.setParamValue(resVal);

                                    etlCfPdsResultVList.add(result);
                                } else if (SUM_SITE_LIST.contains(curSiteName)) {
                                    // 预定义的SUM类型
                                    if (SITE_AVG.equals(curSiteName)) {
                                        etlCellPdsComptSummaryV.setAvg(resVal);
                                    } else if (SITE_MAX.equals(curSiteName)) {
                                        etlCellPdsComptSummaryV.setMax(resVal);
                                    } else if (SITE_MIN.equals(curSiteName)) {
                                        etlCellPdsComptSummaryV.setMin(resVal);
                                    } else if (SITE_SDV.equals(curSiteName)) {
                                        etlCellPdsComptSummaryV.setStd(resVal);
                                        etlCellPdsComptSummaryV.setThreeSigma(resVal * 3);
                                    } else if (SITE_RNG.equals(curSiteName)) {
                                        etlCellPdsComptSummaryV.setRange(resVal);
                                    } else if (SITE_U1.equals(curSiteName)) {
                                        etlCellPdsComptSummaryV.setUniformity1(resVal);
                                    } else if (SITE_U2.equals(curSiteName)) {
                                        etlCellPdsComptSummaryV.setUniformity2(resVal);
                                    }
                                } else {
//                                    logger.error("Raw data 包含无效的字符串，DCDATAID: {}， Site name {}", spcDcdataEntity.getDcdataid(), curSiteName);
                                }
                            } else if (isXData) {
                                etlCellPdsComptSummaryV.setParamValue(resVal);
                                etlCellPdsComptSummaryV.setMax(resVal);
                                etlCellPdsComptSummaryV.setMin(resVal);
                                etlCellPdsComptSummaryV.setAvg(resVal);
                                etlCellPdsComptSummaryV.setDataQty(1.0);
                            } else if (isAscData) {
                                etlCellPdsComptSummaryV.setParamValue2(curResult.getResult());
                                etlCellPdsComptSummaryV.setDataQty(0.0);
                            }
                        }
                        if (isRawData) {
                            etlCellPdsComptSummaryV.setDataQty((double) etlCfPdsResultVList.size());
                            etlCellPdsComptSummaryV.setParamValue(etlCellPdsComptSummaryV.getAvg());
                        }
                        etlCellPdsComptSummaryVList.add(etlCellPdsComptSummaryV);

                    }// End of for Map

                    // save

                    try {
                        save(etlCfPdsGlassV, ErrorLog.ERROR_TABLE_ETL_ARRAY_PDS_GLASS_V, curDCList.get(i).getMaterialname());
                        saveAll(etlCellPdsComptSummaryVList, ErrorLog.ERROR_TABLE_ETL_ARRAY_PDS_GLASS_SUMMARY_V, curDCList.get(i).getMaterialname());
                        saveAll(etlCfPdsResultVList, ErrorLog.ERROR_TABLE_ETL_ARRAY_PDS_RESULT_V, curDCList.get(i).getMaterialname());
                    } catch (Exception e) {
                        logger.info("Cannot excute dao.saveAll() from [{}] to [{}]。", etlConfD.getRunStartTimestamp(), etlConfD.getRunEndTimestamp());
                        throw new ETLException(e.getMessage());
                    }
                    // call proc
                    try {
                        SQLQuery queryDefect = session.createSQLQuery("{Call CELL1_PDS_LOADER.LOAD_PDS()}");
                        queryDefect.executeUpdate();
                    } catch (Exception e) {
//                        System.out.println("======================================================================");
//                        System.out.println(curDCList.get(i).getMaterialname());
//                        System.out.println(curDCList.get(i).getProcessoperationname());
                        logger.error(ErrorLog.ERROR_LOAD_ARRAY_PDS_GLASS_V + ":" + e.getMessage());
                        logger.error(LoggerUtil.stackTraceToString(e));
                        logger.error("Cannot call procedure from [{}] to [{}]。", etlConfD.getRunStartTimestamp(), etlConfD.getRunEndTimestamp());
                        throw new ETLException(curDCList.get(i).getMaterialname() + ":" + ErrorLog.ERROR_LOAD_ARRAY_PDS_GLASS_V + ":" + e.getMessage());
                    }
                    countSize++;
                    try {
                        String hqlPdsSum = "Delete FROM EtlCellPdsComptSummaryV";
                        String hqlPdsGlass = "Delete FROM EtlCellPdsComponentV";
                        String hqlPdsRes = "Delete FROM EtlCellPdsResultV";
                        Query qPdsSum = session.createQuery(hqlPdsSum);
                        Query qPdsGlass = session.createQuery(hqlPdsGlass);
                        Query qPdsRes = session.createQuery(hqlPdsRes);
                        qPdsSum.executeUpdate();
                        qPdsGlass.executeUpdate();
                        qPdsRes.executeUpdate();
//                        logger.info("删除PDS_ETL成功！");
                    } catch (Exception e) {
                        logger.error(LoggerUtil.stackTraceToString(e));
                        throw new ETLException("删除PDS_ETL错误:" + e.getMessage());
                    }
//                }// end of for curDCList
//
//                try {
//                    String hqlCMN = "Delete FROM EtlCellCmnComponentV";
//                    Query qCMN = session.createQuery(hqlCMN);
//                    qCMN.executeUpdate();
////                    logger.info("删除CMN成功！");
//                } catch (Exception e) {
//                    logger.error(LoggerUtil.stackTraceToString(e));
//                    throw new ETLException("删除CMN错误:" + e.getMessage());
//                }

            }
            try {
                String hqlParam = "Delete FROM EtlCellParamV";
                Query qParam = session.createQuery(hqlParam);
                qParam.executeUpdate();
//                logger.info("删除Param成功！");
            } catch (Exception e) {
                logger.error(LoggerUtil.stackTraceToString(e));
                throw new ETLException("删除Param错误:" + e.getMessage());
            }
        } else {
            logger.info("没有此区间的数据。"); // todo print log & save etl data
        }
        return countSize;
    }


    public <T> void save(EtlCellPdsComponentV entities, int errorViewLog, String materialname) throws ETLException {
        try{
            dao3.save(entities);
        }catch (Exception e){
            logger.error(errorViewLog+":"+e.getMessage());
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new ETLException(materialname+":"+errorViewLog+":"+e.getMessage());
        }
    }

    public <T> void saveAll(List<T> entities, int errorViewLog, String materialname) throws ETLException {
        try{
            dao3.saveAll(entities);
        }catch (Exception e){
            logger.error(errorViewLog+":"+e.getMessage());
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new ETLException(materialname+":"+errorViewLog+":"+e.getMessage());
        }
    }

}
